import sqlite3

from log import *
import traceback
import time


DEPOT_NAME = "myDepot.db"

MAX_RETRY = 3
SHORT_PERIOD = 0.2



# Definition of the latest records table which records:
#    (1)Which tables are contained within the DB
#    (2)The latest record time of each table
LATEST_RECORDS = "latestRecords"
TABLE_NAME = "tableName"
TIME = "time"

class MyDB(object):

    def __init__(self):
        pass

    def connect(self, root):
        self.dbRoot = root
        self.dbDepot = self.dbRoot + "\\" + DEPOT_NAME
        Log.t("Database depot: " + self.dbDepot)
        self.connect = sqlite3.connect(self.dbDepot)
        self.cursor = self.connect.cursor()
        self.loadLatestRecordsTable()

    def close(self):
        self.connect.commit()
        self.cursor.close()

    def loadLatestRecordsTable(self):
        cmd = 'CREATE TABLE IF NOT EXISTS "%s" ("%s" VARCHAR(64), "%s" INTEGER)' % (LATEST_RECORDS, TABLE_NAME, TIME)
        self.execute(cmd)
        self.execute('SELECT * FROM "%s"' % LATEST_RECORDS)
        return self.cursor.fetchall()

    def insertLatestRecord(self, tableName, time):
        existed = self.isTableNameExisted(tableName)
        if not existed:
            cmd = 'INSERT INTO %s VALUES("%s", %d)' % (LATEST_RECORDS, tableName, time)
        else:
            cmd = 'UPDATE %s SET "%s"=%d where "%s" = "%s"' % (LATEST_RECORDS, TIME, time, TABLE_NAME, tableName)
        record = self.getLatestRecordByTableName(tableName)
        if (record == None) or (record[1] < time):
            self.execute(cmd)
            Log.t("Table %s: added record of: [%s, %d]" % (LATEST_RECORDS, tableName, time))

    def isTableNameExisted(self, tableName):
        record = self.getLatestRecordByTableName(tableName)
        if (record == None) or (record[0] != tableName):
            return False
        else:
            return True

    def getLatestRecordByTableName(self, tableName):        
        cmd = 'SELECT * FROM "%s"' % LATEST_RECORDS
        self.execute(cmd)
        record = None;
        for eachItem in self.cursor.fetchall():
            if eachItem[0] == tableName:
                record = eachItem
                break
        return record

    def existedRecordInTable(self, tableName, recordTitle):        
        cmd = 'SELECT * FROM "%s"' % tableName
        self.execute(cmd)
        record = None;
        for eachItem in self.cursor.fetchall():
            if eachItem[0] == recordTitle:
                return True
        return False

    def execute(self, cmd):
        for i in range(MAX_RETRY):
            try:
                self.cursor.execute(cmd)
                break
                #Log.t("Succeed in executing DB command: { %s }" % cmd)
            except Exception, e:
                # sleep for a short while, and have another try
                time.sleep(SHORT_PERIOD)
                Log.e("Error: met exception when executing DB command(i=%d): { %s }" % (i, cmd))
                Log.e(traceback.format_exc())

    def formatRecord(self, record):
        if record == None:
            return None

        count = record.__len__()
        msg = '['
        if count == 1:
           msg += record[0]
        else:
            for i in range(count):
                if i < (count - 1):
                    msg += "%s, " % record[i]
                else:
                    msg += "%s" % record[i]
        msg += ']'
        return msg

    @classmethod
    def getCreateTableCommand(cls, tableName, valueNameTypeSizeList):
        cmd = 'CREATE TABLE IF NOT EXISTS "%s" (' % tableName
        count = valueNameTypeSizeList.__len__()
        index = 0
        for vn in valueNameTypeSizeList:
            name = vn[0]
            type = vn[1]
            size = vn[2]
            sufix = '"%s" %s(%d)' % (name, type, size)
            if index == (count - 1):
                cmd += sufix + ")"
            else:
                cmd += sufix + ", "
            index += 1
        return cmd

    @classmethod
    def getInsertRecordCommand(cls, tableName, title, values):
        cmd = ''
        if values != None:
            cmd = 'INSERT INTO "%s" VALUES("%s", ' % (tableName, title)
            index = 0
            count = values.__len__()
            for value in values:
                if index == (count - 1):
                    cmd += '"%s")' % value
                else:
                    cmd += '"%s", ' % value
                index += 1
        else:
            cmd = 'INSERT INTO "%s" VALUES("%s")' % (tableName, title)
        #Log.d(cmd)
        return cmd

    @classmethod
    def getUpdateRecordCommand(cls, tableName, titleName, titleValue, valueNameList, valueList):
        cmd = 'UPDATE %s SET ' % tableName
        count = valueNameList.__len__()
        index = 0
        for index in range(count):
            valueName = valueNameList[index]
            value = valueList[index]
            if index == (count - 1):
                cmd += '"%s"="%s" ' % (valueName, value)
            else:
                cmd += '"%s"="%s",' % (valueName, value)
        cmd += 'where "%s" = "%s"' % (titleName, titleValue)
        #Log.d(cmd)
        return cmd

if __name__ == '__main__':
    import os
    def testLatestRecordTable(db):
        cpi_ch = "cpi_ch"
        ppi_us = "ppi_us"
        cpi_ch_v1 = 39991121
        cpi_ch_v2 = 39991122
        cpi_ch_v3 = 39991120
        ppi_us_v1 = 29991121
        ppi_us_v2 = 29991122
        ppi_us_v3 = 29991120
        
        db.insertLatestRecord(cpi_ch, cpi_ch_v1)
        db.insertLatestRecord(cpi_ch, cpi_ch_v2)
        db.insertLatestRecord(cpi_ch, cpi_ch_v3)
        db.insertLatestRecord(ppi_us, ppi_us_v1)
        db.insertLatestRecord(ppi_us, ppi_us_v2)
        db.insertLatestRecord(ppi_us, ppi_us_v3)
        allItems = db.loadLatestRecordsTable()
        assert(allItems[0] == (cpi_ch, cpi_ch_v2))
        assert(allItems[1] == (ppi_us, ppi_us_v2))

    dbRoot = os.getcwd() + "\\db"
    db = MyDB()
    db.connect(dbRoot)
    testLatestRecordTable(db)
    db.close()